Correlated Subqueries
Some queries require that the output be limited by the results of a subquery. For example, a table may contain a field that includes an effective date and you may want your query to limit the results to the record that contains the latest effective date.
In this example, the “Employee_Emerg_Contact” table from the sample database will be used. This table contains emergency contact information for each employee and is updated whenever contact information changes. The table contains items such as the employee ID number, first and last name, phone number, and effective date. The “effective_date” field contains the date that the record was updated.
The example will find the latest contact information for “Elmer Johnson”, for which multiple records exist containing various effective dates in the database. Elmer Johnson is an emergency contact of employee 018 (Priscilla Johnson) in the Employees Table.
Creating the Main Query
Create the main query using the Employee_Emerg_Contact table. Add lastname, firstname, phone, and effective_date to the SELECT clause. For the WHERE clause, filter by lastname = 'johnson' and firstname = 'elmer'.
Note that this query can return multiple records for that individual.
This query shown below will retrieve three records for Elmer Johnson with effective dates of 1/1/2000, 1/1/2001, and 1/1/2002, each with a different phone number.
SELECT Employee_Emerg_Contact.lastname,
Employee_Emerg_Contact.firstname,
Employee_Emerg_Contact.phone,
Employee_Emerg_Contact.effective_date
FROM Employee_Emerg_Contact
WHERE Employee_Emerg_Contact.lastname ='johnson'
AND Employee_Emerg_Contact.firstname ='elmer'
A subquery is needed (shown in the figure below) to return the record with the latest effective date.
SELECT Max( Employee_Emerg_Contact1.effective_date ) AS Max_effective_date
FROM Employee_Emerg_Contact Employee_Emerg_Contact1
WHERE Employee_Emerg_Contact1.emp_id = Employee_Emerg_Contact.emp_id
Notice that the subquery is “linked” to the main query in the very last line. As each row from the main query is examined, it will be compared against the results of the subquery. These types of subqueries are called “correlated”, as the results of the main query are dependent on the results of the subquery. Also notice that in this example, the main query and the subquery are using the same table. Whenever a table is used multiple times in the same query, it is important to add a table alias for each instance of the table (Argos adds the table aliases automatically). Although both queries use the Employee_Emerg_Contact table, in one case the alias is Employee_Emerg_Contact and in the other it is Employee_Emerg_Contact1. The database considers these as separate tables and does not get confused as long each field is prefaced with the table alias.
Recall that the subquery is to provide a single value for Employee_Emerg_contact.effective_date in the WHERE clause. That is, a multiple lines of SQL are used to provide a value to be used in the WHERE clause.
Creating the Subquery
The steps for creating the subquery shown above will now be discussed. Within the Build Query dialog box, click the Add Query button. The Query dialog box will be displayed again such that you can use it to create the subquery. Click the Show Tables button and select the Employee_Emerg_Contact table. Double-click the effective_date field which moves the field under the SELECT tab as shown below.
Notice that in the subquery, the table name is called Employee_Emerg_Contact1 because the main query and subquery are using the same table (as discussed above).
Click the “Summing” icon and select “Max” since the subquery is to find the latest effective date.
Next create a WHERE statement to link the main query to the subquery using the employee ID as the common field. When using the editor to create the WHERE statement, since the emp_id field exists in both Employee_Emerg_Contact and Employee_Emerg_Contact1 tables, thus both must be referenced.
Select the Employee_Emerg_Contact1 table as shown below.
Click the ellipses button inside the Condition field to launch the SQL Editor.
Click the insert field icon to display a list of database fields. Select Employee_Emerg_contact.emp_id. This now links the emp_id between the main query and the subquery.
The subquery now appears within the Build Query dialog box as shown below.
The subquery is shown below.
select Max( Employee_Emerg_Contact1.effective_date ) as Max_effective_date
from Employee_Emerg_Contact Employee_Emerg_Contact1
where Employee_Emerg_Contact1.emp_id = Employee_Emerg_Contact.emp_id
Placing the Subquery in the Main Query
Now that the main query and subquery have been built, the subquery needs to be placed into the appropriate section of the main query. To perform this linkage, click the “Add this Subquery to the Conditional Fields Tree” button within the Subquery window.
The Conditional Fields (WHERE) tab is displayed at the bottom of the window where you can use the appropriate aggregate function to link the subquery to the main query. In this case choose the = operator since we want the record that has an effective date equal to the effective date returned by the subquery. Make sure to choose the effective_date field from the Employee_Emerg_Contact table in the Main Query.
The process is now complete which creates the complete SQL shown below.
SELECT Employee_Emerg_Contact.lastname,
Employee_Emerg_Contact.firstname,
Employee_Emerg_Contact.phone,
Employee_Emerg_Contact.effective_date
FROM Employee_Emerg_Contact
where Employee_Emerg_Contact.lastname ='johnson'
AND Employee_Emerg_Contact.firstname ='elmer'
AND Employee_Emerg_Contact.effective_date =
( select Max( Employee_Emerg_Contact1.effective_date )
AS Max_effective_date
FROM Employee_Emerg_Contact Employee_Emerg_Contact1
WHERE Employee_Emerg_Contact1.emp_id = Employee_Emerg_Contact.emp_id )
Results
Executing the query yields the results shown below in which the latest effective date for Elmer Johnson is provided.